import xlrd
import copy
import Core.DataSeries
from Core.Gadget import *


# ---批量写入Label(旧格式数据库) as DataSeries---
def LoadWindLabelsOld(database, excelPathfilename, datetime1):

    datetime2 = datetime1 + timedelta(hours=-8)
    book = xlrd.open_workbook(excelPathfilename)

    sheet = book.sheet_by_name("Wind资讯")
    #try:
    #    sheet = book.sheet_by_name("Wind资讯")
    #except:
    #    print("No sheet named Sheet1")

    #获取行数
    nrows = sheet.nrows
    #获取列数
    ncols = sheet.ncols

    #获取第一行第一列数据
    cell_value = sheet.cell_value(1,1)
    #print cell_value

    row_list = []
    for i in range(1,nrows):
        row_data = sheet.row_values(i)
        #row_list.append(row_data)

        #ss = s.split(',')
        ss = row_data
        if ss[ncols-1] == "":
            continue

        symbol = ss[0]
        name = symbol + "_Label"
        dataObjects = DataSeries(name)
        dataObject = {}
        dataObject["DataSeries"] = name
        dataObject["DateTime"] = ToDateTimeString(datetime1)
        dataObject["StdDateTime"] = datetime2
        dataObject["Symbol"] = ss[0]
        dataObject["Key"] = dataObject["DataSeries"] + "_" + dataObject["DateTime"]
        dataObject["Label"] = ss[2]
        labelList = ss[2].split(';')
        dataObject["Labels"] = labelList

        dataObjects.add(dataObject)
        database.saveDataSeries(dataObjects, isUpSert=True)
        kkwood = 1


# ---添加label(旧格式)---
def SetLabels(database, symbol, category, labels, updateDateTime):

    name = symbol + "_Label"
    dataObject = {}
    dataObject["DataSeries"] = name
    dataObject["DateTime"] = ToDateTimeString(updateDateTime)
    dataObject["StdDateTime"] = ToUTCDateTime(updateDateTime)
    dataObject["Key"] = dataObject["DataSeries"] + "_" + dataObject["DateTime"]
    #dataObject["Symbol"] = symbol
    #dataObject["Label"] = ss[2]
    #labelList = ss[2].split(';')
    dataObject["category"] = category

    #---Create DataSeries---
    dataObjects = DataSeries(name)
    dataObjects.add(dataObject)
    database.saveDataSeries(dataObjects, isUpSert=True)
    kkwood = 0


# ---批量写入Labels混合数据库（新格式数据库）---
def LoadWindLabels(database, excelPathfilename, datetime2):

    # ---Prepare a Cache---
    labelsInDatabase0 = database.find("Label", "Label")
    labelsInDatabase = {}
    for doc in labelsInDatabase0:
        label = doc["Label"]
        labelsInDatabase[label] = doc
    #
    nowDateTime = ToUTCDateTime(datetime.now())

    #
    datetime2 = ToUTCDateTime(datetime2)
    book = xlrd.open_workbook(excelPathfilename)

    #
    try:
        sheet = book.sheet_by_name("万得")
        #sheet = book.sheet_by_name("Wind资讯")
    except:
        print("No sheet named Sheet1")

    #获取行数
    nrows = sheet.nrows
    #获取列数
    ncols = sheet.ncols

    #获取第一行第一列数据
    #cell_value = sheet.cell_value(1,1)
    #print cell_value

    row_list = []
    labelModifiedList = []
    for i in range(1,nrows):
        row_data = sheet.row_values(i)
        #row_list.append(row_data)
        if row_data[ncols-1] == "":
            continue

        #
        symbol = row_data[0]
        labelsString = row_data[2]
        print("Process Label: " + symbol + " " + labelsString)

        # --- Fetch Label (of this symbol) historically ---
        labelsSeries = GetDataSeries(database,symbol,datetime2)
        recentLabelDoc = labelsSeries.Last()
        if recentLabelDoc == None:
            recentLabelDoc = {}
            recentLabelDoc["Labels"] = []

        # --- Then determin whom will be Added ---
        labelList = labelsString.split(';')
        for label in labelList:
            #
            if label not in labelModifiedList:
                labelModifiedList.append(label)
            #
            if label not in recentLabelDoc["Labels"]:
                AddLabel(database, symbol, "Label", label, datetime2, addorRemove=True, source="Wind")

        # --- Then determin whom will be Removeed ---
        for label in recentLabelDoc["Labels"]:
            if label not in labelList:
                AddLabel(database, symbol, "Label", label, datetime2, addorRemove=False, source="Wind")

        kkwood = 1
        # ---Loop label in Row ","
    kkwood = 0
    # --- Loop Instruments ---

    # --- Check and Insert to DataBase ---
    for label in labelModifiedList:
        labelDocument = {}
        labelDocument["Label"] = label
        labelDocument["Key"] = label
        if label not in labelsInDatabase.keys():
            labelDocument["Category"] = labelDocument["Category"]
            labelDocument["Source"] = labelDocument["Source"]
            labelDocument["DateTime1"] = labelDocument["StdDateTime"]
            labelDocument["StdDateTime"] = nowDateTime
            labelDocument["CreateDateTime"] = nowDateTime
        #
        labelDocument["DateTime2"] = datetime2
        portfolio = GetLabeledPortfolio(database, label, datetime2)
        labelDocument["NumberOfInstruments"] = len(portfolio)
        #
        database.upsert("Label", "Label", {"Key": label}, labelDocument)
        kkwood = 0


def LoadWindLabelsFolder(database, foldername):
    pass


#---旧格式数据库 to 新格式（Mix labels）
def CovertLabelsToMix(database):
    instruments = database.findAll("Instruments","Stock")
    for instrument in instruments:
        print("Convert Labels of " + instrument["Symbol"])
        symbol = instrument["Symbol"]
        labelSeries = database.getDataSeries(symbol+"_Label")

        lastLabelList = []
        for labelDoc in labelSeries:
            datetime = ToLocalDateTime(labelDoc["StdDateTime"])
            labelList = labelDoc["Labels"]

            #---New Add Label---
            for label in labelList:
                if label not in lastLabelList:
                    AddLabel(database,symbol,"Label",label,datetime, addorRemove = True)
            #---Remove Label---
            for label in lastLabelList:
                if label not in labelList:
                    AddLabel(database,symbol,"Label",label,datetime, addorRemove = False)

            lastLabelList = labelList
        kkwood = 1

# ---2016-10-18新方法---
# ---向数据库Add Remove Label（新格式，混合形式）---
def AddLabel(database, symbol, category, label, updateDateTime, addorRemove = True, source=""):
    #
    dataObject = {}
    dataObject["Symbol"] = symbol
    # dataObject["DataSeries"] = symbol + "_Label"
    dataObject["Label"] = label
    dataObject["Category"] = category
    dataObject["Source"] = source
    if addorRemove:
        dataObject["AddOrRemove"] = "Add"
    else:
        dataObject["AddOrRemove"] = "Remove"

    localDateTime = ToLocalDateTime(updateDateTime)
    datetimeStr = ToDateTimeString(localDateTime)
    # dataObject["DateTime"] = datetimeStr
    dataObject["StdDateTime"] = ToUTCDateTime(updateDateTime)
    dataObject["Key"] = dataObject["Symbol"] + "_" + dataObject["Label"] + "_" + dataObject["AddOrRemove"] + "_Label_" + datetimeStr

    #
    #dataSeriesLabels = database.find("Label", "LabelHistData", endDateTime=updateDateTime, query={"Symbol": symbol, "Category": category, "Label": label})
    #if addorRemove: # Add Label
    #    if dataSeriesLabels.Count() != 0 and dataSeriesLabels.Last()["AddOrRemove"] == "Add":
    #        # Do Nothing
    #        return
    #else: # Remove Label
    #    if dataSeriesLabels.Count() != 0 and dataSeriesLabels.Last()["AddOrRemove"] == "Remove":
    #        # Do Nothing
    #        return

    # Save To Database
    database.upsert("Label", "LabelHistData", {"Key": dataObject["Key"]}, dataObject)

    kkwood = 0


# ---从Labels库收集所有label---
def CollectLabels(database, category = ""):

    #portfolioByLabel = {}
    uniqueLabels = []

    #
    if category == "":
        labels = database.find("Label", "LabelHistData")
    else:
        labels = database.find("Label", "LabelHistData", query={"Category":category})

    #
    maxDateTime = None
    minDateTime = None

    #
    for labelDocument in labels:
        #
        if maxDateTime == None or labelDocument["StdDateTime"] > maxDateTime:
            maxDateTime = labelDocument["StdDateTime"]
        #
        if minDateTime == None or labelDocument["StdDateTime"] < minDateTime:
            minDateTime = labelDocument["StdDateTime"]
        #
        label = labelDocument["Label"]
        if label != "" and label not in uniqueLabels:
            uniqueLabels.append(label)

        # if label not in portfolioByLabel and label != "":
        #        portfolioByLabel[label] = []

    print("Label Range from " + str(minDateTime) + " To " + str(maxDateTime))
    return uniqueLabels


# ---获取某一个指定Stock的Label DataSeries序列---
def GetDataSeries(database, symbol, datetime2=None):
    query = {"Symbol": symbol}
    labelSeries = Core.DataSeries.DataSeries(symbol + "_Label")

    #
    mixLabels = database.find("Label", "LabelHistData", query=query, endDateTime=datetime2, asc=True)
    for labelDoc in mixLabels:
        if labelSeries.Count() == 0:
            doc = {}
            doc["StdDateTime"] = labelDoc["StdDateTime"]
            doc["Labels"] = []
            labelSeries.Add(doc)
        elif labelDoc["StdDateTime"] != labelSeries.Last()["StdDateTime"]:
            doc = {}
            doc["StdDateTime"] = labelDoc["StdDateTime"]
            doc["Labels"] = copy.deepcopy(labelSeries.Last()["Labels"])
            labelSeries.Add(doc)

        if labelDoc["AddOrRemove"] == "Add":
            #if existed, ignore it
            if labelDoc["Label"] not in doc["Labels"]:
                doc["Labels"].append(labelDoc["Label"])

        else:#Remove
            if labelDoc["Label"] in doc["Labels"]:
                doc["Labels"].remove(labelDoc["Label"])

    kkwood = 1
    return labelSeries


#---以某个Label，获取整个Portfolio（股票列表）---
# ---已废弃---
def GetLabeledPortfolioOld(database, targetLabel, datetime):
    portfolio = []
    instruments = database.findAll("Instruments","Stock")
    for instrument in instruments:
        symbol = instrument["Symbol"]
        if symbol == "200002.SZ":
            kkwood = 1
        #print("Find " + targetLabel + " in Symbol " + symbol)
        labelSeries = GetDataSeries(database,symbol)
        labels = labelSeries.Get(datetime,"Previous") #Get Label Not earlier than month back Time
        if labels == None:
            #print("No Labels for " + symbol)
            continue
        for label in labels["Labels"]:
            if label == targetLabel:
                portfolio.append(symbol)

    kkwood = 1
    return portfolio


def GetLabeledPortfolio(database, label, datetime2):
    #
    labelActions = database.find("Label", "LabelHistData", query={"Label":label}, endDateTime=datetime2)
    #
    positons = []
    for labelAction in labelActions:
        symbol = labelAction["Symbol"]
        if labelAction["AddOrRemove"] == "Add":
            if symbol not in positons:
                positons.append(symbol)
        else: # Remove
            if symbol in positons:
                positons.remove(symbol)

    return positons


def FixLabelSource(database):

    docs = database.findAll("Label", "LabelHistData")

    i = 0
    coll = database.client["Label"]["LabelHistData"]
    for doc in docs:
        i = i + 1
        id = doc["_id"]
        #newValue = title + "_" + issuer + "_" + dt
        #newValue = title + "_" + symbol + "_" + issuer + "_" + dt
        update = {}
        update["$set"] = {"Source" : "Wind"}
        print("Update " + str(i))
        coll.update_one({"_id":id}, update, False)


def FixAddLabelTable(database):
    #
    uniqueLabelDocuments = {}
    nowDateTime = ToUTCDateTime(datetime.now())
    database.creatIndex("Label","Label","Key")
    #
    labels = database.find("Label", "LabelHistData")
    for labelDocument in labels:
        #
        label = labelDocument["Label"]
        strDateTime = ToDateTimeString(labelDocument["StdDateTime"])
        print("Processing " + label + " " + strDateTime)
        #
        if labelDocument["Label"] == "":
            pass
        elif labelDocument["Label"] not in uniqueLabelDocuments.keys():
            doc = {}
            doc["Label"] = label
            doc["Category"] = labelDocument["Category"]
            doc["Source"] = labelDocument["Source"]
            doc["DateTime1"] = labelDocument["StdDateTime"]
            doc["DateTime2"] = labelDocument["StdDateTime"]
            doc["StdDateTime"] = nowDateTime
            doc["CreateDateTime"] = nowDateTime
            doc["Key"] = label
            uniqueLabelDocuments[label] = doc
        else:
            doc = uniqueLabelDocuments[label]
            doc["DateTime2"] = labelDocument["StdDateTime"]

    #
    for label, doc in uniqueLabelDocuments.items():
        doc["UpdateDateTime"] = nowDateTime
        datetime2 = doc["DateTime2"]
        portfolio = GetLabeledPortfolio(database, label, datetime2)
        doc["NumberOfInstruments"] = len(portfolio)
        database.upsert("Label","Label", {"Key":label}, doc)
        kkwood = 0



